-- This script demonstrates a seemingly set-based solution, but 
-- which is a loop in disguise.
SET NOCOUNT ON
USE Northgale
go
-- First create user-defined function, the same logic as in 
-- the original loop.
CREATE OR ALTER FUNCTION CustDiscount (@CustomerID nchar(5)) 
                         RETURNS decimal(5,2) AS
BEGIN
   DECLARE @Discount decimal(5,2),
           @City     nvarchar(25),
           @Country  nvarchar(15)

   SELECT @City = City, @Country = Country
   FROM   Customers
   WHERE  CustomerID = @CustomerID

   IF EXISTS (SELECT * 
              FROM   dbo.Discounts 
              WHERE  CustomerID = @CustomerID)
   BEGIN
      SELECT @Discount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID = @CustomerID
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  City = @City)
   BEGIN
      SELECT @Discount = Discount
      FROM   dbo.Discounts
      WHERE  City = @City
   END
   ELSE IF EXISTS (SELECT * 
                   FROM   dbo.Discounts 
                   WHERE  Country = @Country)
   BEGIN
      SELECT @Discount = Discount
      FROM   dbo.Discounts
      WHERE  Country = @Country
   END 
   ELSE IF EXISTS (SELECT *
                   FROM  Discounts
                   WHERE CustomerID IS NULL
                     AND City       IS NULL
                     AND Country    IS NULL) 
   BEGIN 
      SELECT @Discount = Discount
      FROM   dbo.Discounts
      WHERE  CustomerID IS NULL
        AND  City       IS NULL
        AND  Country    IS NULL 
   END
   ELSE 
      SELECT @Discount = 0

   RETURN @Discount
END
go
-- Set up new discounts for the test.
EXEC setup_discounts

DECLARE @starttime datetime2(3) = sysdatetime(),
        @afterms   int

CREATE TABLE #tmporders (OrderID int NOT NULL PRIMARY KEY)

INSERT #tmporders(OrderID)
   SELECT OrderID FROM dbo.OrdersToModify()

UPDATE dbo.Orders
SET    Discount    = dbo.CustDiscount(CustomerID),
       TotalAmount = OD.ProductSum * 
                     (1 - dbo.CustDiscount(CustomerID)/100) + O.Freight
FROM   dbo.Orders O
JOIN   (SELECT OrderID, SUM(Quantity * UnitPrice) AS ProductSum
        FROM   dbo.[Order Details]
        GROUP  BY OrderID) AS OD ON OD.OrderID = O.OrderID
WHERE  O.Discount <> dbo.CustDiscount(CustomerID)
  AND  EXISTS (SELECT *
               FROM   #tmporders t
               WHERE  t.OrderID = O.OrderID)

SELECT @afterms = datediff(ms, @starttime, sysdatetime())
RAISERROR('All rows processed in %d ms.', 0, 1, @afterms) WITH NOWAIT
go
DROP TABLE #tmporders
go
/* Recreate the indexes and redo.
CREATE INDEX CustomerID_ix ON Discounts(CustomerID) INCLUDE (Discount)
CREATE INDEX City_ix ON Discounts(City) INCLUDE (Discount)
CREATE INDEX Country_ix ON Discounts(Country) INCLUDE (Discount)
*/